package Dao.Impl;

import Bean.Furn;
import Bean.Page;
import Bean.User;
import Dao.BasicDao;
import Dao.FurnDao;
import Utils.DBHelp;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.List;

public class FurnDaoImpl extends BasicDao implements FurnDao {

    @Override
    public Furn getFurn(String name, String Merchant) {
        String sql ="select id, FurnName, Merchant, Price, Volume, inventory, img_path imgPath from Furn where FurnName = ? and Merchant = ?;";
        Object o = selectOne(sql, Furn.class, name, Merchant);
        Furn furn = (Furn) o;
        return furn;
    }

    @Override
    public List<Furn> getAllFurn() {
        String sql = "select id, FurnName, Merchant, Price, Volume, inventory, img_path imgPath from Furn;";
        return queryMulti(sql,Furn.class);
    }

    @Override
    public boolean InsertFurn(Furn furn) {
        String sql = "insert into furn ( FurnName, Merchant, Price, Volume, inventory, img_path) VALUES (?,?,?,?,?,?);";
        QueryRunner queryRunner = new QueryRunner();
        int update = 0;

        try {
           update = queryRunner.update(DBHelp.getConnection(),
                    sql, furn.getFurnName(), furn.getMerchant(), furn.getPrice(), furn.getVolume(), furn.getInventory(), furn.getImgPath());
            return true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            if (update == 1){
                return true;
            }else {
                return false;
            }
        }
    }

    public boolean deleteFurn(String FurnName,String Merchant) {
        String sql = "delete from furn where FurnName = ? and Merchant = ?;";
        return dml(sql,FurnName,Merchant);
    }

    @Override
    public boolean updateFurn(Integer id ,Furn furn) {
        String sql = "update Furn" + " "+
                "set FurnName=?,Merchant=?,Price=?,Volume=?,inventory=?,img_path=?"+
                "where id=?;";
        return dml(sql,furn.getFurnName(),furn.getMerchant(),furn.getPrice(),furn.getVolume(),furn.getInventory(),
                furn.getImgPath(),id);
    }

    @Override
    public Furn getOneFurn (String sql  ,Object... nums) {
        return (Furn) selectOne(sql,Furn.class,nums);
    }
    @Override
    public Integer getCount(String sql,Object... nums) {
        QueryRunner queryRunner = new QueryRunner();
        Integer index = 0;
        try {
            Long update = (Long)queryRunner.query(DBHelp.getConnection(), sql,new ScalarHandler(),nums);
            index = update.intValue();
        } catch (SQLException e) {
            index = -1;
        }
        return index;
    }

    @Override
    public List<Furn> getSomeFurn(String sql, Page page) {
        sql = "select  * from furn limit ? offset ?;";
        return getSomeQuery(sql,Furn.class,page.getNumberSize(),(page.getBeginIndex()-1)*page.getNumberSize());
    }

    @Override
    public Page<Furn> getPage(int begin, int pageSuze) {
        Page<Furn> furnPage = new Page<>();
        furnPage.setBeginIndex(begin);
        furnPage.setNumberSize(pageSuze);
        Integer count = getCount("select  count(*) from furn;");
        furnPage.setSum(count);
        int PageCount = 0;
        if (count % pageSuze == 0){
            PageCount = count/ pageSuze;
        }else {
            PageCount = count/ pageSuze+1;
        }
        // 一共有多少组
        furnPage.setMaxSize(PageCount);
        String sql ="select  * from furn limit ? offset ?;";
        furnPage.setItem(getSomeFurn(sql,furnPage));
        return furnPage;
    }

    @Override
    public List<Furn> Search(String keyWord, int begin, int Size) {
        // 使用存储过程
        String sql = "call SearchFurnPage(?,?,?);";
        List<Furn> queryMulti =  getSomeQuery(sql,Furn.class,keyWord,(begin-1)*Size,Size);
        return queryMulti;
    }

    public static void main(String[] args) {
        FurnDaoImpl furnDao = new FurnDaoImpl();
        String keyWord = "%小椅子%";
        int begin = 1;
        int Size = 4;
        String sql = "select count(*) from furn where FurnName like ?;";
        Integer count = furnDao.getCount(sql, keyWord);
        System.out.println(count);
        List<Furn> search = furnDao.Search(keyWord, begin, Size);

        for (Furn furn :search) {
            System.out.println(furn);
        }


    }
}
